﻿using DevComponents.DotNetBar.SuperGrid;
using Spire.Xls;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace ZNYBSProject
{
    public partial class frmInitPipeData : Form
    {
        public frmInitPipeData()
        {
            InitializeComponent();
        }

        Workbook workbook = new Workbook();//创建excel文档
        string ExcelPath;
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog pOpenDia = new OpenFileDialog();
                pOpenDia.Filter = "(*.Excel工作簿)|*.xls;*.xlsx";
                if (pOpenDia.ShowDialog() == DialogResult.OK)
                {
                    ExcelPath = pOpenDia.FileName;
                    txtexcel.Text = ExcelPath;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        
        string savePath;
        private void savebtn_Click(object sender, EventArgs e)
        {
            SaveFileDialog pSaveFileDialog = new SaveFileDialog();
            pSaveFileDialog.Filter = "(*.xlsx)|*.xlsx";
            pSaveFileDialog.FileName = "成果数据";
            if (pSaveFileDialog.ShowDialog() == DialogResult.OK)
            {
                filepathtxt.Text = pSaveFileDialog.FileName;
                savePath = pSaveFileDialog.FileName;
            }
        }

        private void Btnexport_Click(object sender, EventArgs e)
        {
            intDic();//初始化字典

            Dictionary<string, string>[] fieldDic = traversalAllRows(superGridControl1);

            exportXlsx(fieldDic, savePath, sourceTable);

            if (nofindID.Count > 0)
            {
                MessageBox.Show("转换完成，尚有未匹配的编码：" + Array2Str(nofindID));
            }
            else if (nofindID.Count == 0)
            {
                MessageBox.Show("转换完成!");
            }
        }

        #region 保存Excel文件
        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="workbook">workBook</param>
        /// <param name="FileName">文件名称</param>
        public void SavaXls(Workbook workbook, string ExcelFilePth)
        {
            if (!string.IsNullOrEmpty(ExcelFilePth))
            {
                workbook.SaveToFile(ExcelFilePth, ExcelVersion.Version2010);
            }
            else
            {
                //workbook.SaveToFile(ExcelFilePth + DateTime.Now.ToString("yyyyMMddhhmmssfff") + ".xlsx", ExcelVersion.Version2010);
            }
        }
        #endregion

        /// <summary>
        /// 获取点线表的字段
        /// </summary>
        /// <param name="xlspath"></param>
        /// <returns></returns>
        private Dictionary<string, Dictionary<string, string>> getTableFileds(string xlspath)
        {
            Dictionary<string, Dictionary<string, string>> resultDic = new Dictionary<string, Dictionary<string, string>>();
            Workbook swk = new Workbook();
            swk.LoadFromFile(xlspath);
            Worksheet sheet = swk.Worksheets[0];

            for (int row = 2; row < sheet.Rows.Count(); row++)
            {
                string key = sheet.Range[row, 3].NumberText.Trim();
                if (!resultDic.ContainsKey(key))
                {
                    Dictionary<string, string> subDic = new Dictionary<string, string>();
                    resultDic.Add(key, subDic);
                }

                string name = sheet.Range[row, 5].NumberText.Trim();
                string title = sheet.Range[row, 4].NumberText.Trim();
                if (!resultDic[key].ContainsKey(name))
                {
                    resultDic[key].Add(name, title);
                }
            }
            return resultDic;
        }


        #region GetNewRow
        /// <summary>
        /// Create a new random Employee row
        /// </summary>
        /// <param name="count"></param>
        /// <returns></returns>
        private void GetNewRow(string name, string title)
        {
            superGridControl1.BeginUpdate();
            GridPanel panel = superGridControl1.PrimaryGrid;
            object[] ob1 = new object[] { name, title ,null,null};
            GridRow row=new GridRow(ob1);
            panel.Rows.Add(row);
            superGridControl1.EndUpdate();
            panel.AutoExpandSetGroup = true;
        }
        #endregion


        #region 初始化supergird
        /// <summary>
        /// Routine to add a random set of rows to the grid.
        /// </summary>
        private void InitComField(object[] list)
        {
            var girdc = superGridControl1.PrimaryGrid.Columns[2];
            girdc.EditorType = typeof(MyComboBox);
            girdc.EditorParams = new object[] { list };
            var girdc1 = superGridControl1.PrimaryGrid.Columns[3];
            girdc1.EditorType = typeof(MyComboBox);
            var list1 =new object[] {"编号转换","字符拼接" };
            girdc1.EditorParams = new object[] { list1 };
        }
        #endregion

        #region 遍历superGridControl1的行        
        private Dictionary<string, string>[] traversalAllRows(SuperGridControl superGridControl1)
        {
            Dictionary<string, string>[] fieldDic = new Dictionary<string, string>[2];
            Dictionary<string, string> ofield_nfieldDic = new Dictionary<string, string>();
            Dictionary<string, string> nfield_ofieldDic = new Dictionary<string, string>();
            GridPanel gp = superGridControl1.PrimaryGrid;
            for (int i = 0; i < gp.Rows.Count; i++)
            {
                GridRow pgr = (GridRow)gp.Rows[i];
                if (pgr.Rows.Count >= 1)
                {           
                }
                else
                {
                    string ofield = pgr[0].Value.ToString();
                    string nfield="";
                    if (pgr[2].Value != null)
                    {
                        if (pgr[3].Value != null)
                        {
                            nfield = pgr[2].Value.ToString()+","+pgr[3].Value.ToString();
                        }
                        else
                        {
                              nfield = pgr[2].Value.ToString();
                        }

                        if (!nfield_ofieldDic.ContainsKey(nfield))
                        {
                            nfield_ofieldDic.Add(nfield,ofield);
                        }
                    }
             
                    if (!ofield_nfieldDic.ContainsKey(ofield))
                    {
                        ofield_nfieldDic.Add(ofield, nfield);
                    }
                }
            }
            fieldDic[0] = ofield_nfieldDic;
            fieldDic[1] = nfield_ofieldDic;
            return fieldDic;
        }
        #endregion


        /// <summary>
        /// 导出成果
        /// </summary>
        /// <param name="ofield_nfieldDic">标准字段与待处理字段的映射关系</param>
        /// <param name="xlspath">保存路径</param>
        /// <param name="srcTable">待处理数据集</param>
        private void exportXlsx(Dictionary<string, string>[] fieldDic, string xlspath, DataTable srcTable)
        {
            Dictionary<string, string> ofield_nfieldDic = fieldDic[0];
            Dictionary<string, string> nfield_ofieldDic = fieldDic[1];
            Workbook wb = new Workbook();
            Worksheet sheet = wb.Worksheets[0];
            int col=1;
            Dictionary<string, int> ofield_col_Dic = new Dictionary<string, int>();//ofield 与列对应关系
            foreach (string colname in ofield_nfieldDic.Keys)
            {
                sheet.Range[1, col].Value = colname;
                ofield_col_Dic.Add(colname,col);
                col++;
            }

            int row=2;
            string CHR ="";
            string AUX ="";
            string  PLPT0="";
            string  PLPT1="";

            for (int i = 0; i < srcTable.Rows.Count; i++)
            {
                DataRow dr = srcTable.Rows[i];    
                foreach(string nfield in nfield_ofieldDic.Keys)
                {
                    int rc = ofield_col_Dic[nfield_ofieldDic[nfield]];//当前值要写入的列编号
                    string value = "";
                    if (nfield.Contains(','))
                    {
                        if (nfield.Split(',')[1] == "编号转换")
                        {
                            if (nfield_ofieldDic[nfield] == "MNTPCD")
                            {
                                if (kind == "1")
                                {
                                    CHR = ofield_nfieldDic["CHR"];
                                    AUX = ofield_nfieldDic["AUX"];
                                    value = Title2MNTPCD(srcTable.Rows[i][0].ToString(), srcTable.Rows[i][CHR].ToString(), srcTable.Rows[i][AUX].ToString());
                                }
                                else if (kind == "2")
                                {
                                    value = TitleLine2MNTPCD(srcTable.Rows[i][0].ToString());
                                }
                            }
                            else if (nfield_ofieldDic[nfield] == "AUXNO")
                            {
                                value = Title2AUXCD(srcTable.Rows[i][0].ToString(), srcTable.Rows[i][AUX].ToString());
                            }
                            
                        }
                        else if (nfield.Split(',')[1] == "字符拼接" && kind=="2")
                        {
                            PLPT0 = ofield_nfieldDic["PLPT0"];
                            PLPT1 = ofield_nfieldDic["PLPT1"];
                            string PID0 = srcTable.Rows[i][PLPT0].ToString();
                            string PID1 = srcTable.Rows[i][PLPT1].ToString();
                            value = Union2Str(PID0, PID1);
                        }
                        else if (nfield.Split(',')[1] == "计算长度" && kind == "2")
                        {
                            PLPT0 = ofield_nfieldDic["PLPT0"];
                            PLPT1 = ofield_nfieldDic["PLPT1"];

                        }
                    }
                    else
                    {
                        value = srcTable.Rows[i][nfield].ToString();
                    }
                    sheet.Range[row, rc].Value = value;
                }
                row++;
            }

            SavaXls(wb, savePath);
        }

        #region 两个字符拼接，这里指的是起点编号和终点编号
        private string Union2Str(string str1, string str2)
        {
            string str = str1 + "_" + str2;
            return str;
        }
        #endregion


        #region 将附属物和特征文字转为对应的编号，特征和附属物都有时，默认取附属物，无附属物则取特征
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pipeTypeID"></param>
        /// <param name="CHR">特征</param>
        /// <param name="AUX">附属物</param>
        /// <returns></returns>
        private string Title2MNTPCD(string pipeTypeID, string CHR, string AUX)
        {
            //默认源数据的第一列是管网的类型编码
            string pipeTypeTitle = pipeTypeDic[pipeTypeID];
            string queryAUXKey = pipeTypeTitle + "," + AUX;
             string queryCHRKey = pipeTypeTitle + "," + CHR;
            string MNTPCD = "";
            if (featureTypeDic.ContainsKey(queryAUXKey))
            {
                MNTPCD = featureTypeDic[queryAUXKey];
            }
            else if (featureTypeDic.ContainsKey(queryCHRKey))
            {
                MNTPCD = featureTypeDic[queryCHRKey];
            }

            return MNTPCD;
        }

        /// <summary>
        /// 计算线表的MNTPCD
        /// </summary>
        /// <param name="pipeTypeID"></param>
        /// <returns></returns>
        ArrayList nofindID = new ArrayList();
        private string TitleLine2MNTPCD(string pipeTypeID)
        {
            //默认源数据的第一列是管网的类型编码
             string MNTPCD = "";
             if (pipeTypeDic.ContainsKey(pipeTypeID))
             {
                 string pipeTypeTitle = pipeTypeDic[pipeTypeID];


                 if (pipeLineDic.ContainsKey(pipeTypeID))
                 {
                     MNTPCD = pipeLineDic[pipeTypeID];
                 }
             }
             else
             {
                 if (!nofindID.Contains(pipeTypeID))
                 {
                     nofindID.Add(pipeTypeID);
                 }
             }
           
            return MNTPCD;
        }
        
        #endregion

        #region Array2Str
        private string Array2Str(ArrayList alist)
        {
            string restr="";
            foreach (string str in alist)
            {
                restr += str + ",";
            }
            return restr;
        }

        #endregion


        #region 将附属物和特征文字转为对应的编号，特征和附属物都有时，默认取附属物，无附属物则取特征
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pipeTypeID"></param>
        /// <param name="CHR">特征</param>
        /// <param name="AUX">附属物</param>
        /// <returns></returns>
        private string Title2AUXCD(string pipeTypeID,  string AUX)
        {
            //默认源数据的第一列是管网的类型编码
            string pipeTypeTitle = pipeTypeDic[pipeTypeID];
            string queryAUXKey = pipeTypeTitle + "," + AUX;
            string AUXCD = "";
            if (featureTypeDic.ContainsKey(queryAUXKey))
            {
                AUXCD = featureTypeDic[queryAUXKey];
            }
            return AUXCD;
        }
        #endregion

        #region 通过spire.xls 读取Excel
        //加载Excel
        /// <summary>
        ///  通过spire.xls 读取Excel 默认读取第一个sheet
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="isFirstRowColumn">第一行是否是标题，true为是，false为否</param>
        /// <returns></returns>
        private  DataTable LoadDataFromExcel(string filePath, bool isFirstRowColumn)
        {
            try
            {
                Workbook workbook = new Workbook();//创建excel文档
                workbook.LoadFromFile(filePath);
                Worksheet sheet = workbook.Worksheets[0];
                int n = workbook.Worksheets.Count;

                int fcolumn = sheet.Columns.Count();
                int rowCount = sheet.Rows.Count();
                //由于通过columncount获取的列数与实际的不一致，因此在这里要进行判断
                int colcount = fcolumn;//column是实际有值的列
                for (int col = 1; col <= fcolumn; col++)
                {
                    string hasStr = "";
                    for (int r = 1; r <= rowCount; r++)
                    {
                        hasStr += sheet.Range[r, col].NumberText.Trim();
                        if (hasStr != "") break;
                    }
                    if (hasStr == "")
                    {
                        colcount = col - 1;
                        break;
                    }
                }

                DataTable pTable = new DataTable();
                Spire.Xls.CellRange[] allcells = sheet.Rows;
                Spire.Xls.CellRange firstRow = sheet.Rows[0];
                //int cellCount = firstRow.LastRow; //一行最后一个cell的编号 即总的列数

                if (isFirstRowColumn)
                {
                    //先给datatable增加列，然后再给table的每一行赋值
                    for (int i = 1; i <= colcount; i++)
                    {
                        DataColumn column = new DataColumn(sheet.Range[1, i].NumberText.Trim());
                        pTable.Columns.Add(column);
                    }

                    for (int r = 2; r <= rowCount; r++)
                    {
                        DataRow dr = pTable.NewRow();
                        for (int c = 1; c <= fcolumn; c++)
                        {
                            dr[c - 1] = sheet.Range[r, c].NumberText.Trim();
                        }
                        pTable.Rows.Add(dr);
                    }
                }
                return pTable;
            }
            catch (Exception err)
            {
                MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }
        }
        #endregion
        DataTable sourceTable = null;
        string kind = "";//用于标识点表还是线表，点表为"1"，线表为"2"
        private void button2_Click(object sender, EventArgs e)
        {
            if (radioButton1.Checked)
            {
                kind = "1";
            }
            else if (radioButton2.Checked)
            {
                kind = "2";
            }
           
            //打开待处理的表格，获取第一行的值，作为下拉选择项
            sourceTable = LoadDataFromExcel(ExcelPath, true);//sourceTable 待处理的表
            object[] list = new object[sourceTable.Columns.Count];
            for (int col = 0; col < sourceTable.Columns.Count; col++)
            {
                list[col] = sourceTable.Columns[col].ColumnName;
            }

            string dic3str = Application.StartupPath + @"\dicdata\数据库表结构.xlsx";
            Dictionary<string, Dictionary<string, string>> tablefieldDic = getTableFileds(dic3str);

            //点表
            InitComField(list);
            foreach (string name in tablefieldDic[kind].Keys)
            {
                GetNewRow(name, tablefieldDic[kind][name]);
            }
        }

        #region 初始化字典
        Dictionary<string, string> featureTypeDic = new Dictionary<string, string>();
        Dictionary<string, string> pipeTypeDic = new Dictionary<string, string>();
        Dictionary<string, string> pipeLineDic = new Dictionary<string, string>();
        private void intDic()
        {
            nofindID.Clear();
            //加载字典表pipe_type_own.xls 
            string dic1str = Application.StartupPath + @"\dicdata\pipe_type_own.xlsx";
            DataTable pipeTypeTable = LoadDataFromExcel(dic1str, true);//pipeTypeTable 对应pipe_type_own.xlsx
            for (int row = 1; row < pipeTypeTable.Rows.Count; row++)
            {
                string key = pipeTypeTable.Rows[row][5].ToString();
                string value = pipeTypeTable.Rows[row][4].ToString();
        
                string SUBMNTPCD = pipeTypeTable.Rows[row][8].ToString();
                if (!pipeTypeDic.ContainsKey(key))
                {
                    pipeTypeDic.Add(key, value);
                }
                if (!pipeLineDic.ContainsKey(key))
                {
                    pipeLineDic.Add(key, SUBMNTPCD);
                }
            }

            //加载字典表pipe_featuretype.xls
            string dic2str = Application.StartupPath + @"\dicdata\pipe_featuretype.xlsx";
            DataTable pipeFeatTypeTable = LoadDataFromExcel(dic2str, true);//pipeFeatTypeTable 对应pipe_featuretype.xlsx
            for (int row = 1; row < pipeFeatTypeTable.Rows.Count; row++)
            {
                string key = pipeFeatTypeTable.Rows[row][2].ToString() + "," + pipeFeatTypeTable.Rows[row][3].ToString();
                string value = pipeFeatTypeTable.Rows[row][4].ToString();
                if (!featureTypeDic.ContainsKey(key))
                {
                    featureTypeDic.Add(key, value);
                }
            }
        }
        #endregion
    }
}



public class MyComboBox : GridComboBoxExEditControl
{
    public MyComboBox(object source)
    {
        DataSource = source;
    }
}


public class Vector3
{
    float x;
    float y;
    float z;
    public Vector3(float x, float y, float z)
    {
        this.x = x;
        this.y = y;
        this.z = z;
    }
 
    public static double Distance(Vector3 startAirportPos, Vector3 endAirportPos)
    {
        double sqrt = System.Math.Sqrt(System.Math.Pow(startAirportPos.x - endAirportPos.x, 2) + System.Math.Pow(startAirportPos.y - endAirportPos.y, 2) + System.Math.Pow(startAirportPos.z - endAirportPos.z, 2));
        return System.Math.Abs(sqrt);
    }
}
